﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using QuanLyDoanVien.General;
using System.Data;
using System.Data.SqlClient;
using QuanLyDoanVien.Bussiness;

namespace QuanLyDoanVien.DataLayer
{
    class HoatDongData
    {
        DataService m_HoatDongData = new DataService();
        public List<HoatDongInfo> layDSHoatDong()
        {
            List<HoatDongInfo> rs = new List<HoatDongInfo>();
            DataService.OpenConnection();
            SqlCommand cmd = new SqlCommand("SELECT * FROM Tab_HoatDong");
            m_HoatDongData.Load(cmd);
            foreach (DataRow dr in m_HoatDongData.Rows)
            {
                HoatDongInfo hd = new HoatDongInfo();
                hd.MaHoatDong = dr["MaHoatDong"].ToString();
                hd.TenHoatDong = dr["TenHoatDong"].ToString();
                hd.NgayToChuc = DateTime.Parse(dr["NgayToChuc"].ToString());
                hd.NgayKetThuc = DateTime.Parse(dr["NgayKetThuc"].ToString());
                hd.Diem = float.Parse(dr["Diem"].ToString());
                hd.GhiChu = dr["GhiChu"].ToString();
                rs.Add(hd);
            }
            m_HoatDongData.CloseConnection();
            return rs;
        }

        public HoatDongInfo layHoatDong(string maHD)
        {
            HoatDongInfo hd = null;
            string sql = "select * from Tab_HoatDong where MaHoatDong=@MaHoatDong";
            SqlCommand command = new SqlCommand(sql);
            command.Parameters.Add("MaHoatDong", SqlDbType.Char).Value = maHD;
            m_HoatDongData.Load(command);
            if (m_HoatDongData.Rows.Count == 0)
                return null;
            DataRow dr = m_HoatDongData.Rows[0];
            hd = new HoatDongInfo();
            hd.MaHoatDong = dr["MaHoatDong"].ToString();
            hd.TenHoatDong=dr["TenHoatDong"].ToString();
            hd.NgayKetThuc =DateTime.Parse( dr["NgayKetThuc"].ToString());
            hd.NgayToChuc=DateTime.Parse(dr["NgayToChuc"].ToString());
            hd.Diem=float.Parse(dr["Diem"].ToString());
            hd.GhiChu=dr["GhiChu"].ToString();
            return hd;
        }

        #region Them
    
        public bool LuuHoatDong(HoatDongInfo hd)
        {
            DataService.OpenConnection();
            SqlCommand cmd = new SqlCommand("INSERT INTO Tab_HoatDong VALUES(@MaHoatDong, @TenHoatDong, @NgayToChuc, @NgayKetThuc, @Diem, @GhiChu)");
            cmd.Parameters.Add("MaHoatDong", SqlDbType.Char).Value = hd.MaHoatDong;
            cmd.Parameters.Add("TenHoatDong", SqlDbType.NVarChar).Value = hd.TenHoatDong;
            cmd.Parameters.Add("NgayToChuc", SqlDbType.DateTime).Value = hd.NgayToChuc;
            cmd.Parameters.Add("NgayKetThuc", SqlDbType.DateTime).Value = hd.NgayKetThuc;
            cmd.Parameters.Add("Diem", SqlDbType.Float).Value = hd.Diem;
            cmd.Parameters.Add("GhiChu", SqlDbType.NVarChar).Value = hd.GhiChu;
            try
            {
                m_HoatDongData.ExecuteNoneQuery(cmd);
                m_HoatDongData.CloseConnection();
                return true;
            }
            catch(Exception ex)
            {
                m_HoatDongData.CloseConnection();
                return false;
             }
           
        }
        #endregion

        #region Sua
        public bool SuaHoatDong(HoatDongInfo hd)
        {
            DataService.OpenConnection();
            SqlCommand cmd = new SqlCommand("UPDATE Tab_HoatDong SET TenHoatDong = @TenHoatDong, NgayToChuc = @NgayToChuc, NgayKetThuc = @NgayKetThuc, Diem = @Diem, GhiChu =@GhiChu WHERE MaHoatDong = @MaHoatDong");
            cmd.Parameters.Add("MaHoatDong", SqlDbType.Char).Value = hd.MaHoatDong;
            cmd.Parameters.Add("TenHoatDong", SqlDbType.NVarChar).Value = hd.TenHoatDong;
            cmd.Parameters.Add("NgayToChuc", SqlDbType.DateTime).Value = hd.NgayToChuc;
            cmd.Parameters.Add("NgayKetThuc", SqlDbType.DateTime).Value = hd.NgayKetThuc;
            cmd.Parameters.Add("Diem", SqlDbType.Float).Value = hd.Diem;
            cmd.Parameters.Add("GhiChu", SqlDbType.NVarChar).Value = hd.GhiChu;
            try
            {
                m_HoatDongData.ExecuteNoneQuery(cmd);
                m_HoatDongData.CloseConnection();
                return true;
            }
            catch
            {
                m_HoatDongData.CloseConnection();
                return false;
            }
        }
        #endregion

        #region Xoa
        public bool XoaHoatDong(String mahd)
        {
            DataService.OpenConnection();
            SqlCommand cmd = new SqlCommand("DELETE FROM Tab_HoatDong WHERE MaHoatDong = @MaHoatDong");
            cmd.Parameters.Add("MaHoatDong", SqlDbType.Char).Value = mahd;
            try
            {
                m_HoatDongData.ExecuteNoneQuery(cmd);
                m_HoatDongData.CloseConnection();
                return true;
            }
            catch
            {
                m_HoatDongData.CloseConnection();
                return false;
            }
        }

        #endregion

        public string getAutoID()
        {
            DataService.OpenConnection();
            QuyDinh qd = new QuyDinh();
            string id = "";
            string sql = "select top 1 MaHoatDong from Tab_HoatDong Order By MaHoatDong desc";
            SqlCommand command = new SqlCommand(sql);
            m_HoatDongData.Load(command);
            if (m_HoatDongData.Rows.Count < 1)
                return "HD00000001";
            id = m_HoatDongData.Rows[0][0].ToString();
            int num = int.Parse(id.Substring(2, 8)) + 1;
            id = "HD" + qd.LaySTT(num);
            m_HoatDongData.CloseConnection();
            return id;
        }

       

    }
}
